﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using VK.Comm.TransactSql;
using System.Data;
using CommonLib;
using VK.Comm;

namespace VK.Casemeet.ADs
{
    internal static class ADDAL
    {
        internal static ADEntity GetADByReader(IDataReader dr)
        {
            ADEntity AD = null;

            AD = new ADEntity();
            AD.ADID = dr.GetInt32(0);
            AD.Image = dr.IsDBNull(1) ? null : dr.GetString(1);
            AD.Description = dr.IsDBNull(2) ? null : dr.GetString(2);
            AD.Width = dr.IsDBNull(3) ? 0 : dr.GetInt32(3);
            AD.Height = dr.IsDBNull(4) ? 0 : dr.GetInt32(4);
            AD.Url = dr.IsDBNull(5) ? null : dr.GetString(5);
            AD.Target = dr.IsDBNull(6) ? 0 : dr.GetInt32(6);
            AD.IsEnable = dr.GetBoolean(7);
            AD.Code = dr.IsDBNull(8) ? null : dr.GetString(8);
            AD.ProvinceArea = dr.IsDBNull(9) ? "全部" : dr.GetString(9);
            return AD;
        }

        internal static int GetADCount(IList<Condition> conditionArray)
        {
            Database db = DatabaseFactory.CreateDatabase();
            int result = 0;

            string SelectAD = @"SELECT COUNT([AD].[ADID]) FROM [AD] LEFT OUTER JOIN Area AS ProvinceArea ON [AD].ProvinceID = ProvinceArea.AreaID";

            List<DbParameter> parameterlst = new List<DbParameter>();
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectAD, ref parameterlst);
            DbCommand SelectADCommand = db.GetSqlStringCommand(SelectAD);
            DbHelper.PackDbCommandByDbParameters(SelectADCommand, parameterlst.ToArray());

            object obj = db.ExecuteScalar(SelectADCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        internal static List<ADEntity> GetADList(int startRecordIndex, int endRecordIndex, IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<ADEntity> ADlst = new List<ADEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectAD = @"
                SELECT [ADID],[Image],[Description],[Width],[Height],[Url],[Target],[IsEnable],[Code],[ProvinceArea]
	            FROM (
                    SELECT [AD].[ADID],[AD].[Image],[AD].[Description],[AD].[Width],[AD].[Height],[AD].[Url],
                    [AD].[Target],[AD].[IsEnable],[AD].[Code],ProvinceArea.Name AS ProvinceArea
                    ,ROW_NUMBER() OVER (ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectAD += " " + sortField + " " + sortMode + ",";
            SelectAD += @" [ADID] Desc
                ) AS rowNum 
                    FROM [AD] LEFT OUTER JOIN Area AS ProvinceArea ON [AD].ProvinceID = ProvinceArea.AreaID ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectAD, ref parameterlst);
            SelectAD += @"
                ) AS [AD] 
	            WHERE rowNum BETWEEN @StartRecordIndex and @EndRecordIndex ";

            DbCommand SelectADCommand = db.GetSqlStringCommand(SelectAD);
            parameterlst.Add(DbHelper.PackInParameter(db, "@StartRecordIndex", DbType.Int32, startRecordIndex));
            parameterlst.Add(DbHelper.PackInParameter(db, "@EndRecordIndex", DbType.Int32, endRecordIndex));
            DbHelper.PackDbCommandByDbParameters(SelectADCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectADCommand))
            {
                while (dr.Read())
                {
                    ADEntity AD = GetADByReader(dr);

                    ADlst.Add(AD);
                }
            }

            return ADlst;
        }

        internal static ADEntity GetAD(int ADID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            ADEntity AD = null;

            string SelectAD = @"
                SELECT [AD].[ADID],[AD].[Image],[AD].[Description],[AD].[Width],[AD].[Height],[AD].[Url],
                    [AD].[Target],[AD].[IsEnable],[AD].[Code],ProvinceArea.Name AS ProvinceArea
                FROM [AD] LEFT OUTER JOIN Area AS ProvinceArea ON [AD].ProvinceID = ProvinceArea.AreaID 
                WHERE ADID=@ADID
	           ";

            DbCommand SelectADCommand = db.GetSqlStringCommand(SelectAD);
            db.AddInParameter(SelectADCommand, "@ADID", DbType.Int32, ADID);

            using (IDataReader dr = db.ExecuteReader(SelectADCommand))
            {
                if (dr.Read())
                {
                    AD = GetADByReader(dr);
                }
            }

            return AD;
        }

        internal static ADEntity GetAD(string code, int provinceID, int cityID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            ADEntity AD = null;

            string SelectAD = @"
                SELECT [AD].[ADID],[AD].[Image],[AD].[Description],[AD].[Width],[AD].[Height],[AD].[Url],
                    [AD].[Target],[AD].[IsEnable],[AD].[Code],ProvinceArea.Name AS ProvinceArea
                FROM [AD] LEFT OUTER JOIN Area AS ProvinceArea ON [AD].ProvinceID = ProvinceArea.AreaID  
                WHERE [AD].[Code]=@Code ";
            if (provinceID != 0)
            {
                SelectAD += " AND [AD].ProvinceID=@ProvinceID ";
            }
            if (cityID != 0)
            {
                SelectAD += " AND [AD].CityID=@CityID ";
            }

            DbCommand SelectADCommand = db.GetSqlStringCommand(SelectAD);
            db.AddInParameter(SelectADCommand, "@Code", DbType.String, code);
            if (provinceID != 0)
            {
                db.AddInParameter(SelectADCommand, "@ProvinceID", DbType.Int32, provinceID);
            }
            if (cityID != 0)
            {
                db.AddInParameter(SelectADCommand, "@CityID", DbType.Int32, cityID);
            }

            using (IDataReader dr = db.ExecuteReader(SelectADCommand))
            {
                if (dr.Read())
                {
                    AD = GetADByReader(dr);
                }
            }

            return AD;
        }

        internal static ADEntity[] GetADArray()
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<ADEntity> ADlst = new List<ADEntity>();

            string SelectAD = @"
                SELECT [AD].[ADID],[AD].[Image],[AD].[Description],[AD].[Width],[AD].[Height],[AD].[Url],
                    [AD].[Target],[AD].[IsEnable],[AD].[Code],ProvinceArea.Name AS ProvinceArea
                FROM [AD] LEFT OUTER JOIN Area AS ProvinceArea ON [AD].ProvinceID = ProvinceArea.AreaID
                ORDER BY [AD].[ADID] Asc";

            DbCommand SelectExtentCommand = db.GetSqlStringCommand(SelectAD);

            using (IDataReader dr = db.ExecuteReader(SelectExtentCommand))
            {
                while (dr.Read())
                {
                    ADEntity AD = GetADByReader(dr);

                    ADlst.Add(AD);
                }
            }

            return ADlst.ToArray();
        }

        internal static int InsertAD(ADEntity AD)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string InsertAD = @"
            INSERT INTO [AD]
            ([Image],[Description],[Width],[Height],[Url],[Target],[IsEnable],[Code])
             VALUES
            (@Image,@Description,@Width,@Height,@Url,@Target,@IsEnable,@Code)";

            DbCommand InsertADCommand = db.GetSqlStringCommand(InsertAD);
            db.AddInParameter(InsertADCommand, "@Image", DbType.String, AD.Image);
            db.AddInParameter(InsertADCommand, "@Description", DbType.String, AD.Description);
            db.AddInParameter(InsertADCommand, "@Width", DbType.Int32, AD.Width);
            db.AddInParameter(InsertADCommand, "@Height", DbType.Int32, AD.Height);
            db.AddInParameter(InsertADCommand, "@Url", DbType.String, AD.Url);
            db.AddInParameter(InsertADCommand, "@Target", DbType.Int32, AD.Target);
            db.AddInParameter(InsertADCommand, "@IsEnable", DbType.Boolean, AD.IsEnable);
            db.AddInParameter(InsertADCommand, "@Code", DbType.String, AD.Code);
            result = db.ExecuteNonQuery(InsertADCommand);

            return result;
        }

        internal static int UpdateAD(ADEntity AD)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();
            string UpdateAD = @"
            UPDATE [AD]
            SET [Image]=@Image,[Description]=@Description,[Width]=@Width,[Height]=@Height,[Url]=@Url,
                [Target]=@Target,[IsEnable]=@IsEnable,[Code]=@Code
             WHERE [ADID] = @ADID";

            DbCommand UpdateADCommand = db.GetSqlStringCommand(UpdateAD);
            db.AddInParameter(UpdateADCommand, "@Image", DbType.String, AD.Image);
            db.AddInParameter(UpdateADCommand, "@Description", DbType.String, AD.Description);
            db.AddInParameter(UpdateADCommand, "@Width", DbType.Int32, AD.Width);
            db.AddInParameter(UpdateADCommand, "@Height", DbType.Int32, AD.Height);
            db.AddInParameter(UpdateADCommand, "@Url", DbType.String, AD.Url);
            db.AddInParameter(UpdateADCommand, "@Target", DbType.Int32, AD.Target);
            db.AddInParameter(UpdateADCommand, "@IsEnable", DbType.Boolean, AD.IsEnable);
            db.AddInParameter(UpdateADCommand, "@ADID", DbType.Int32, AD.ADID);
            db.AddInParameter(UpdateADCommand, "@Code", DbType.String, AD.Code);
            result = db.ExecuteNonQuery(UpdateADCommand);

            return result;
        }

        internal static int DeleteAD(int ADID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string DeleteAD = @"
            DELETE FROM [AD] WHERE ADID=@ADID";

            DbCommand DeleteADCommand = db.GetSqlStringCommand(DeleteAD);
            db.AddInParameter(DeleteADCommand, "@ADID", DbType.Int32, ADID);
            result = db.ExecuteNonQuery(DeleteADCommand);

            return result;
        }

        internal static int SetADEnable(int ADID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetAD = @"UPDATE [AD]
                   SET [IsEnable] = ([IsEnable]+1)%2
                 WHERE [ADID] = @ADID";

            DbCommand SetADCommand = db.GetSqlStringCommand(SetAD);
            db.AddInParameter(SetADCommand, "@ADID", DbType.Int32, ADID);
            result = db.ExecuteNonQuery(SetADCommand);

            return result;
        }

        internal static void SetPass()
        {
            Database db = DatabaseFactory.CreateDatabase();
            Dictionary<string, string> albumlst = new Dictionary<string, string>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectAlbum = @"
                    SELECT [LoginId],[Pwdcn]
                    FROM [UserInfo] 
                    WHERE UserType=1;";

            DbCommand SelectAlbumCommand = db.GetSqlStringCommand(SelectAlbum);
            DbHelper.PackDbCommandByDbParameters(SelectAlbumCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectAlbumCommand))
            {
                while (dr.Read())
                {
                    string key = dr.IsDBNull(0) ? null : dr.GetString(0);
                    if (!albumlst.Keys.Contains(key))
                    {
                        albumlst.Add(
                            key,
                            dr.IsDBNull(1) ? null : dr.GetString(1)
                            );
                    }
                }
            }
            //StringBuilder InsertAlbum = new StringBuilder();
            //foreach (KeyValuePair<string, string> a in albumlst)
            //{
            //    string paw = "";
            //    try
            //    {
            //        paw = EncryptHelper.AesDecrypt(a.Value, a.Key);
            //    }
            //    catch { }
            //    InsertAlbum.Append("UPDATE UserInfo SET Pwdcn='").Append(paw).Append("' WHERE [LoginId]='").Append(a.Key).Append("';");
            //}

            //DbCommand InsertAlbumCommand = db.GetSqlStringCommand(WrapSql.WrapTransaction("Tran_InsertAlbum", InsertAlbum.ToString()));
            //object obj = db.ExecuteScalar(InsertAlbumCommand);

            StringBuilder InsertAlbum = new StringBuilder();
            foreach (KeyValuePair<string, string> a in albumlst)
            {
                string paw = "";
                try
                {
                    paw = DES.Encrypt(a.Value, CS.MemberPassKey);
                }
                catch { }
                InsertAlbum.Append("UPDATE UserInfo SET Pwdcncn='").Append(paw).Append("' WHERE [LoginId]='").Append(a.Key).Append("';");
            }
            DbCommand InsertAlbumCommand = db.GetSqlStringCommand(WrapSql.WrapTransaction("Tran_InsertAlbum", InsertAlbum.ToString()));
            object obj = db.ExecuteScalar(InsertAlbumCommand);
        }
    }
}
